-- 会议室预定系统数据库初始化脚本
-- 数据库: PostgreSQL
-- 用途: 创建表结构和初始化数据

-- ============================================
-- 1. 创建数据库（如果不存在）
-- ============================================
-- CREATE DATABASE meeting_room_db;

-- ============================================
-- 2. 创建表结构
-- ============================================

-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_role CHECK (role IN ('user', 'admin'))
);

-- 会议室表
CREATE TABLE IF NOT EXISTS rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    capacity INTEGER NOT NULL,
    location VARCHAR(200),
    facilities TEXT[],
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_status CHECK (status IN ('active', 'maintenance', 'inactive'))
);

-- 预定表
CREATE TABLE IF NOT EXISTS bookings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    booking_person VARCHAR(100) NOT NULL,
    meeting_content TEXT NOT NULL,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'confirmed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_booking_status CHECK (status IN ('pending', 'confirmed', 'cancelled', 'completed'))
);

-- 时间表表
CREATE TABLE IF NOT EXISTS schedules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    day_of_week INTEGER NOT NULL,
    open_time TIME NOT NULL,
    close_time TIME NOT NULL,
    is_available BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_day_of_week CHECK (day_of_week BETWEEN 0 AND 6),
    CONSTRAINT unique_room_day UNIQUE (room_id, day_of_week)
);

-- 系统配置表
CREATE TABLE IF NOT EXISTS system_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 3. 创建索引
-- ============================================

-- 预定表索引（用于时间查询优化）
CREATE INDEX IF NOT EXISTS idx_bookings_room_id ON bookings(room_id);
CREATE INDEX IF NOT EXISTS idx_bookings_start_time ON bookings(start_time);
CREATE INDEX IF NOT EXISTS idx_bookings_end_time ON bookings(end_time);
CREATE INDEX IF NOT EXISTS idx_bookings_status ON bookings(status);

-- 时间表索引
CREATE INDEX IF NOT EXISTS idx_schedules_room_id ON schedules(room_id);
CREATE INDEX IF NOT EXISTS idx_schedules_day_of_week ON schedules(day_of_week);

-- 用户表索引
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- ============================================
-- 4. 插入初始数据
-- ============================================

-- 插入管理员用户
-- 密码: admin (使用bcrypt加密)
INSERT INTO users (username, email, password_hash, role)
VALUES 
    ('admin', 'admin@example.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYzpLaEhK9K', 'admin'),
    ('demo', 'demo@example.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYzpLaEhK9K', 'user')
ON CONFLICT (username) DO NOTHING;

-- 插入会议室
INSERT INTO rooms (name, capacity, location, facilities, status)
VALUES 
    ('会议室A', 10, 'A栋3楼', ARRAY['投影仪', '白板', '视频会议'], 'active'),
    ('会议室B', 20, 'A栋3楼', ARRAY['投影仪', '白板', '音响系统'], 'active'),
    ('301会议室', 6, 'B栋3楼', ARRAY['白板', '电视屏幕'], 'active'),
    ('董事会议室', 15, 'A栋5楼', ARRAY['投影仪', '视频会议', '高级座椅'], 'active')
ON CONFLICT (name) DO NOTHING;

-- 为每个会议室创建默认时间表（周一至周五 08:00-18:00，周末关闭）
INSERT INTO schedules (room_id, day_of_week, open_time, close_time, is_available)
SELECT 
    r.id,
    d.day,
    CASE 
        WHEN d.day BETWEEN 0 AND 4 THEN '08:00:00'::TIME
        ELSE '00:00:00'::TIME
    END,
    CASE 
        WHEN d.day BETWEEN 0 AND 4 THEN '18:00:00'::TIME
        ELSE '00:00:00'::TIME
    END,
    CASE 
        WHEN d.day BETWEEN 0 AND 4 THEN TRUE
        ELSE FALSE
    END
FROM 
    rooms r
CROSS JOIN 
    (SELECT generate_series(0, 6) AS day) d
ON CONFLICT (room_id, day_of_week) DO NOTHING;

-- 插入系统配置（管理员密码）
-- 默认密码: 123456789 (使用bcrypt加密)
INSERT INTO system_configs (config_key, config_value, description)
VALUES 
    ('admin_password', '$2b$12$8gqVZ8qKJZY6ZJYFJdYJZ.YFJdYJZ.YFJdYJZ.YFJdYJZ.YFJdYJZ', '管理员登录密码')
ON CONFLICT (config_key) DO UPDATE SET
    updated_at = CURRENT_TIMESTAMP;

-- ============================================
-- 5. 创建更新时间戳触发器函数
-- ============================================

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 为所有表添加更新时间戳触发器
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_rooms_updated_at ON rooms;
CREATE TRIGGER update_rooms_updated_at
    BEFORE UPDATE ON rooms
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_bookings_updated_at ON bookings;
CREATE TRIGGER update_bookings_updated_at
    BEFORE UPDATE ON bookings
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_schedules_updated_at ON schedules;
CREATE TRIGGER update_schedules_updated_at
    BEFORE UPDATE ON schedules
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_system_configs_updated_at ON system_configs;
CREATE TRIGGER update_system_configs_updated_at
    BEFORE UPDATE ON system_configs
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================
-- 6. 验证数据
-- ============================================

-- 查看用户数量
SELECT '用户数量:' AS info, COUNT(*) AS count FROM users;

-- 查看会议室数量
SELECT '会议室数量:' AS info, COUNT(*) AS count FROM rooms;

-- 查看时间表数量
SELECT '时间表数量:' AS info, COUNT(*) AS count FROM schedules;

-- 查看系统配置数量
SELECT '系统配置数量:' AS info, COUNT(*) AS count FROM system_configs;

-- ============================================
-- 说明
-- ============================================
/*
初始化完成后，系统包含：
1. 2个用户账号：
   - 管理员: username='admin', password='admin'
   - 演示用户: username='demo', password='demo'

2. 4个会议室：
   - 会议室A (10人)
   - 会议室B (20人)
   - 301会议室 (6人)
   - 董事会议室 (15人)

3. 每个会议室的时间表（7天）：
   - 周一至周五: 08:00-18:00 (开放)
   - 周六至周日: 关闭

4. 系统配置：
   - 管理员密码: 123456789 (默认)

使用方法：
1. 连接到PostgreSQL数据库
2. 执行此脚本: psql -U postgres -d meeting_room_db -f init_db.sql
3. 或在pgAdmin中直接运行
*/
